﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using IronOne.SalesOne.BusinessObjects;
using System.Linq.Dynamic;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqProspect
    {

        public List<Prospect> GetProspects(string sortExpression)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<ProspectEntity> query = db.ProspectEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "ProspectID":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.ProspectID);
                            else
                                query = query.OrderByDescending(c => c.ProspectID);
                            break;

                        case "CustomerID":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.CustomerID);
                            else
                                query = query.OrderByDescending(c => c.CustomerID);
                            break;

                    }
                }
                return query.Select(c => Mapper.ToProspectObject(c)).ToList();
            }
        }


        public Prospect GetProspect(string prospectId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToProspectObject(db.ProspectEntities
                            .SingleOrDefault(p => p.ProspectID == prospectId));
            }
        }

        public void InsertProspect(Prospect prospect)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    ProspectEntity entity = Mapper.ToProspectEntity(prospect);
                    db.ProspectEntities.InsertOnSubmit(entity);
                    db.SubmitChanges();

                    prospect.ProspectId = entity.ProspectID; ;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to insert prospect record was made before your changes.");
                }
                catch (Exception)
                {
                    throw new Exception("Unable to Insert a prospect ");
                    //throw new Exception("Unable to Insert a prospect " + prospect.ProspectId);
                }
            }
        }

        public int UpdateProspect(Prospect prospect)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    ProspectEntity entity = Mapper.ToProspectEntity(prospect);
                    db.ProspectEntities.Attach(entity, true);
                    db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to update prospect record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public int DeleteProspect(Prospect prospect)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    ProspectEntity entity = Mapper.ToProspectEntity(prospect);
                    db.ProspectEntities.Attach(entity, false);
                    db.ProspectEntities.DeleteOnSubmit(entity);
                    db.SubmitChanges();

                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to delete prospect record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public Prospect GetProspForCustomer(string customerId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToProspectObject(db.ProspectEntities
                            .SingleOrDefault(p => p.CustomerID == customerId));
            }
        }


        //public List<Prospect> SearchProspects(ContactSearch pros)
        //{
        //    //StringBuilder sb = new StringBuilder();
            
            



        //    StringBuilder str = new StringBuilder();

        //    //unit
        //    if (pros.Unt != null)
        //    {
        //        int j = 0;
        //        foreach (SalesOneUnit un in pros.Unt)
        //        {

        //            if (j == 0)
        //            {
        //                if (un.UnitId != 0 )
        //                    str.Append(" AND (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

        //            }
        //            else
        //            {
        //                if (un.UnitId != 0 )
        //                    str.Append(" OR (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

        //            }

        //            j++;
        //        }
                
        //    }
            


        //    //branch
        //    if (pros.Brnch != null)
        //    {
        //        int k = 0;
        //        foreach (Branch br in pros.Brnch)
        //        {

        //            if (k == 0)
        //            {
        //                if (br.BranchId != 0 && pros.Unt == null)
        //                    str.Append(" AND (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
        //            }
        //            else
        //            {
        //                if (br.BranchId != 0 && pros.Unt == null)
        //                    str.Append(" OR (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
        //            }

        //            k++;
        //        }
        //    }
            

            
        //    //so
        //    StringBuilder cus = new StringBuilder();
        //    if (pros.SOCode != null)
        //    {
        //        int i = 0;
        //        foreach (string so in pros.SOCode)
        //        {
        //            if (i == 0)
        //            {
        //                if (!String.IsNullOrEmpty(so) && so != "Select")
        //                    cus.Append(" AND SOCode = \"" + so + "\"");
        //            }
        //            else
        //            {
        //                if (!String.IsNullOrEmpty(so) && so != "Select")
        //                    cus.Append(" OR SOCode = \"" + so + "\"");
        //            }

        //            i++;
        //        } 
        //    }
            



        //    string status = "Prospect";

        //    cus.Append("Status = \"" + status + "\"");
        //    if (!String.IsNullOrEmpty(pros.CliemtId))
        //        cus.Append(" AND CustomerID = \"" + pros.CliemtId + "\"");
        //    if (!String.IsNullOrEmpty(pros.Name))
        //        cus.Append(" AND FirstName = \"" + pros.Name + "\"");
        //    if (!String.IsNullOrEmpty(pros.IdNo) && !String.IsNullOrEmpty(pros.IdType))
        //        cus.Append("IDNumber = \"" + pros.IdNo + "\"" + " AND IDType = " + pros.IdType);
            
        //    if (!String.IsNullOrEmpty(pros.Contact))
        //        cus.Append(" AND ContactNo = " + pros.Contact);


        //    if (str.Length > 1)
        //    {

        //        if (cus.ToString().StartsWith(" AND "))
        //            cus.Remove(0, 5);
        //        if (str.ToString().StartsWith(" AND "))
        //            str.Remove(0, 5);


        //        using (SODataDataContext db = DataContextFactory.CreateContext())
        //        {
        //            IQueryable<ProspectEntity> query = db.ProspectEntities;

        //            if (str.Length > 1)
        //            {
        //                if (cus.Length > 1)
        //                {
        //                    query = from pol in (db.ProspectEntities.Where(sb.ToString()))
        //                            join cust in
        //                                (from c in db.CustomerEntities.Where(cus.ToString())
        //                                 join us in db.UsersInStructureEntities.Where(str.ToString())
        //                                 on c.UserID equals us.UserId
        //                                 select c)

        //                            on pol.CustomerID equals cust.CustomerID
        //                            select pol;


        //                    //query = from pol in (db.ProspectEntities.Where(sb.ToString()))
        //                    //        join cust in
        //                    //            (from c in db.CustomerEntities.Where(cus.ToString())
        //                    //             join us in db.UsersInStructureEntities.Where(str.ToString())
        //                    //             on c.UserID equals us.UserId
        //                    //             select c)

        //                    //        on pol.CustomerID equals cust.CustomerID
        //                    //        select pol;
        //                }
        //                //else if (sb.Length > 1 && cus.Length < 1)
        //                //{
        //                //    query = from pol in (db.ProspectEntities.Where(sb.ToString()))
        //                //            join cust in
        //                //                (from c in db.CustomerEntities
        //                //                 join us in db.UsersInStructureEntities.Where(str.ToString())
        //                //                 on c.UserID equals us.UserId
        //                //                 select c)
        //                //            on pol.CustomerID equals cust.CustomerID
        //                //            select pol;
        //                //}
        //                else if (sb.Length < 1 && cus.Length > 1)
        //                {
        //                    query = from pol in db.ProspectEntities
        //                            join cust in
        //                                (from c in db.CustomerEntities.Where(cus.ToString())
        //                                 join us in db.UsersInStructureEntities.Where(str.ToString())
        //                                 on c.UserID equals us.UserId
        //                                 select c)
        //                            on pol.CustomerID equals cust.CustomerID
        //                            select pol;
        //                }
        //                //else if (sb.Length < 1 && cus.Length < 1)
        //                //{
        //                //    query = from pol in db.ProspectEntities
        //                //            join cust in
        //                //                (from c in db.CustomerEntities
        //                //                 join us in db.UsersInStructureEntities.Where(str.ToString())
        //                //                 on c.UserID equals us.UserId
        //                //                 select c)
        //                //            on pol.CustomerID equals cust.CustomerID
        //                //            select pol;
        //                //}
        //            }
        //            else if (str.Length < 1)
        //            {
        //                if (sb.Length > 1 && cus.Length > 1)
        //                {
        //                    query = from pol in (db.ProspectEntities.Where(sb.ToString()))
        //                            join cust in (db.CustomerEntities.Where(cus.ToString()))
        //                            on pol.CustomerID equals cust.CustomerID
        //                            select pol;
        //                }
        //                //else if (sb.Length > 1 && cus.Length < 1)
        //                //{
        //                //    query = from pol in (db.ProspectEntities.Where(sb.ToString()))
        //                //            join cust in db.CustomerEntities
        //                //            on pol.CustomerID equals cust.CustomerID
        //                //            select pol;
        //                //}
        //                else if (sb.Length < 1 && cus.Length > 1)
        //                {
        //                    query = from pol in db.ProspectEntities
        //                            join cust in (db.CustomerEntities.Where(cus.ToString()))
        //                            on pol.CustomerID equals cust.CustomerID
        //                            select pol;
        //                }
        //            }



        //            return query.Select(c => Mapper.ToProspectObject(c)).ToList();
        //        }
        //    }
        //    else
        //        return null;
        //}
    }
}
